package com.campus.dao.impl;

import com.campus.dao.StudentDao;
import com.campus.model.Class;
import com.campus.model.Student;
import com.campus.model.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

@Repository
@Slf4j
public class StudentDaoImpl implements StudentDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 新增学员
    @Override
    @Transactional
    public boolean addStudent(Student student, User user) {
        String insertUserSql = "INSERT INTO t_sys_user (login_name, password, gender, birthday, phone, email, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
        String insertRoleSql = "INSERT INTO t_sys_user_role (user_id, role_id) VALUES (?, ?)";
        String insertStudentSql = "INSERT INTO t_manager_student (stu_name, from_place, parent_phone, current_grade, is_first, comment, status, login_id) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        // 检查登录名是否已经存在
        if (isValidLoginName(user.getLoginName())) {
            log.error("登录名已经存在: {}", user.getLoginName());
            return false;
        }

        try {
            // 1. 插入到 t_manager_user 表
            jdbcTemplate.update(insertUserSql, user.getLoginName(), "123456", user.getGender(),
                    new java.sql.Date(user.getBirthday().getTime()), user.getPhone(), user.getEmail(), 0);//状态默认是0，未启用。

            // 2. 获取新插入的 LoginId
            Integer loginId = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Integer.class);

            if (loginId == null) {
                return false;
            }

            // 3. 插入到 t_sys_user_role 表，绑定角色
            jdbcTemplate.update(insertRoleSql, loginId, 4);  // 2 表示学生角色

            // 4. 插入到 t_manager_student 表
            student.setLoginId(loginId); // 设置 loginId
            int rowsAffected = jdbcTemplate.update(insertStudentSql, student.getStuName(), student.getFromPlace(),
                    student.getParentPhone(), student.getCurrentGrade(), student.getIsFirst(), student.getComment(), 0, student.getLoginId());

            return rowsAffected > 0;

        } catch (Exception e) {
            // 如果发生异常，Spring 会自动回滚事务
            log.error("Error adding student: {}", e.getMessage());
            return false;
        }
    }

    // 批量导入学员
    @Override
    @Transactional
    public boolean batchAddStudents(List<Student> students, List<User> users) {
        if (students.size() != users.size()) {
            throw new IllegalArgumentException("学生和用户列表的大小必须相同！");
        }

        String insertUserSql = "INSERT INTO t_sys_user (login_name, password, gender, birthday, phone, email, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
        String insertRoleSql = "INSERT INTO t_sys_user_role (user_id, role_id) VALUES (?, ?)";
        String insertStudentSql = "INSERT INTO t_manager_student (stu_name, from_place, parent_phone, current_grade, is_first, comment, status, login_id) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        try {
            for (int i = 0; i < students.size(); i++) {
                User user = users.get(i);
                Student student = students.get(i);

                // 检查登录名是否已经存在
                if (isValidLoginName(user.getLoginName())) {
                    log.error("登录名已经存在: {}", user.getLoginName());
                    continue;  // 如果存在，跳过当前学生
                }

                // 1. 插入用户表 t_sys_user
                jdbcTemplate.update(insertUserSql, user.getLoginName(), "123456", user.getGender(),
                        new java.sql.Date(user.getBirthday().getTime()), user.getPhone(), user.getEmail(), 0); // 状态默认是 0

                // 2. 获取新插入的 LoginId
                Integer loginId = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Integer.class);
                if (loginId == null) {
                    throw new RuntimeException("获取 LoginId 失败！");
                }

                // 3. 插入角色表 t_sys_user_role
                jdbcTemplate.update(insertRoleSql, loginId, 4); // 假设角色 ID 4 表示学生

                // 4. 插入学生表 t_manager_student
                student.setLoginId(loginId); // 设置 LoginId
                jdbcTemplate.update(insertStudentSql, student.getStuName(), student.getFromPlace(),
                        student.getParentPhone(), student.getCurrentGrade(), student.getIsFirst(),
                        student.getComment(), 0, loginId); // 状态默认是 0
            }
            return true; // 批量插入成功
        } catch (Exception e) {
            // 如果发生异常，Spring 会自动回滚事务
            e.printStackTrace();
            return false;
        }
    }

    // 审核学员
    @Override
    public int updateStudentStatus(Integer studentId, String status) {
        String sql = "UPDATE t_manager_student SET status = ? WHERE id = ?";
        return jdbcTemplate.update(sql, status, studentId);
    }

    // 批量审核学员
    @Override
    public int batchUpdateStudentStatus(List<Integer> studentIds, String status) {
        String sql = "UPDATE t_manager_student SET status = ? WHERE id = ?";
        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, status);
                ps.setInt(2, studentIds.get(i));
            }

            @Override
            public int getBatchSize() {
                return studentIds.size();
            }
        }).length; // 返回批量操作的数量
    }

    // 更新学生信息
    @Override
    public boolean updateStudent(Integer studentId, Student updatedStudent) {
        String sql = "UPDATE t_manager_student SET stu_name = ?, from_place = ?, parent_phone = ?, current_grade = ?, is_first = ?, comment = ? WHERE id = ?";
        int rowsAffected = jdbcTemplate.update(sql, updatedStudent.getStuName(), updatedStudent.getFromPlace(),
                updatedStudent.getParentPhone(), updatedStudent.getCurrentGrade(), updatedStudent.getIsFirst(),
                updatedStudent.getComment(), studentId);
        return rowsAffected > 0;
    }

    @Override
    public int deleteStudentById(Integer id) {
        // 1. 获取与学生关联的 login_id
        String getLoginIdSql = "SELECT login_id FROM t_manager_student WHERE id = ?";
        Integer loginId = jdbcTemplate.queryForObject(getLoginIdSql, Integer.class, id);

        if (loginId == null) {
            // 如果没有找到对应的学生，返回 0 表示未删除
            return 0;
        }

        // 2. 删除学生信息
        String deleteStudentSql = "DELETE FROM t_manager_student WHERE id = ?";
        int studentDeleted = jdbcTemplate.update(deleteStudentSql, id);

        if (studentDeleted > 0) {
            // 3. 删除 sys_user_role 表中与用户关联的角色信息
            String deleteUserRoleSql = "DELETE FROM t_sys_user_role WHERE user_id = ?";
            jdbcTemplate.update(deleteUserRoleSql, loginId);

            // 4. 删除与学生关联的用户信息
            String deleteUserSql = "DELETE FROM t_sys_user WHERE id = ?";
            jdbcTemplate.update(deleteUserSql, loginId);
        }

        return studentDeleted; // 返回删除的学生记录行数
    }

    @Override
    public List<Student> findAllStudents() {
        String sql = "SELECT * FROM t_manager_student";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class));
    }

    // Check if login_name is duplicate
    private boolean isValidLoginName(String loginName) {
        String sql = "SELECT COUNT(*) FROM t_sys_user WHERE login_name = ?";
        try {
            Integer count = jdbcTemplate.queryForObject(sql, new Object[]{loginName}, Integer.class);
            return count > 0;  // If count > 0, login name is duplicated
        } catch (EmptyResultDataAccessException e) {
            // No result found, login name is valid (not duplicated)
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;  // If there's an error, consider it invalid
        }
    }

}
